"""
计算哪些股票的资金是持续流出的

这个要简单点，只关注资金

"""

import time, datetime
import pymysql
import pandas as pd

pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 500)

# 忽略警告
import warnings

warnings.filterwarnings("ignore")

# 链接mysql
# 本机
conn1 = pymysql.connect(host='localhost', user='root', passwd='root', db='stock', charset="utf8")
# 阿里云试用
conn3 = pymysql.connect(host='rm-2vc83ot65e551h73beo.mysql.cn-chengdu.rds.aliyuncs.com', port=3306, user='river', passwd='lihua201314SJ', db='stock', charset="utf8")

conn = conn3
cursor = conn.cursor()

sql = """
SELECT DISTINCT `code`,name,day_no,price,change_rate,main_money_num 
FROM stock.`stock_history_money_d` 
WHERE price>0 and day_no>=DATE_SUB(CURDATE(),INTERVAL 30 DAY)
and code regexp '^00|^60' 
and name not regexp 'ST'
order by `code`,day_no
"""
# 获取所有的股票资金信息
df_code_history_money = pd.read_sql(sql, conn)

# 提出股票代码清单
code_list = df_code_history_money['code'].drop_duplicates().to_list()


def change_money_unit(money):
    """将金钱的单位从元改为xx亿或xx万，方便阅读"""
    if abs(money) >= 100000000:
        return f'{round(money / 100000000, 2)}亿'
    elif abs(money) >= 10000:
        return f'{round(money / 10000, 2)}万'
    else:
        return money


def change_unit(num):
    if num[-1] == '亿':
        return float(num[:-1]) * 100000000
    elif num[-1] == '万':
        return float(num[:-1]) * 10000
    elif num == '' or num == ' ' or num == '-':
        return 0
    else:
        return int(num[:-3])


# 新的df，用来存储结果
df_code_continue = pd.DataFrame()
df_code_continue_latest = pd.DataFrame()

# 最小主力资金
MINI_MONEY = 0

# 累计最小主力资金,10的7次方，就是1的后面7个零，就是一千万
MINI_MONEY_SUM = 10 ** 7

# 股票被选中的数量
num = 0

for code in code_list:
    df_code = df_code_history_money[df_code_history_money['code'] == code]
    # 资金持续流入的天数
    money_continue_days = 0
    money_continue_days_list = []
    # 资金持续流入的金额
    money_continue_sum = 0
    money_continue_sum_list = []
    # 累计值涨幅
    change_rate_sum = 0
    change_rate_sum_list = []
    # 设置需要保留的行
    keep_lines = []
    # 是否有一天的资金大于一个亿
    is_day_money_greater_1_yi = 0
    for item in df_code.to_dict('records'):
        day_no = item['day_no']
        name = item['name']
        main_money_num = item['main_money_num']
        change_rate = item['change_rate']
        # 当天的主力资金流入是否大于0
        if main_money_num > MINI_MONEY:
            money_continue_days = money_continue_days + 1
            money_continue_sum = money_continue_sum + main_money_num
            change_rate_sum = change_rate_sum + change_rate
        else:
            money_continue_days = 0
            money_continue_sum = 0
            change_rate_sum = 0
        money_continue_days_list.append(money_continue_days)
        money_continue_sum_list.append(money_continue_sum)
        change_rate_sum_list.append(change_rate_sum)
        # 需要保留的行
        if money_continue_days > 0:
            keep_lines.append(1)
        else:
            keep_lines = [0 for a in keep_lines]
            keep_lines.append(0)
    df_code['money_continue_days'] = money_continue_days_list
    df_code['money_continue_sum'] = money_continue_sum_list
    df_code['change_rate_sum'] = change_rate_sum_list
    df_code['keep_lines'] = keep_lines
    # 连续3天流入，平均每天流入要超过1000万
    if (money_continue_days == 3 and money_continue_sum >= MINI_MONEY_SUM * 3) or \
            (money_continue_days >= 4 and money_continue_sum >= MINI_MONEY_SUM * 4):
        df_code2 = df_code[df_code['keep_lines'] == 1]
        del df_code2['keep_lines']
        print(df_code2)
        df_code_continue = df_code_continue.append(df_code2)
        df_code_continue_latest = df_code_continue_latest.append(df_code2.tail(1))
        print('- - ' * 30)
        num = num + 1
        # break

print(f"共有{num}只股票被选中！")

# TODO 单个结果单
# 排序
df_code_continue_latest = df_code_continue_latest.sort_values(['money_continue_days', 'money_continue_sum'], ascending=[False, False])

# 处理内容格式
df_code_continue_latest['change_rate'] = df_code_continue_latest['change_rate'].map(lambda x: f'{x}%')
df_code_continue_latest['main_money_num'] = df_code_continue_latest['main_money_num'].apply(change_money_unit)
df_code_continue_latest['money_continue_sum'] = df_code_continue_latest['money_continue_sum'].apply(change_money_unit)
df_code_continue_latest['change_rate_sum'] = df_code_continue_latest['change_rate_sum'].map(lambda x: f'{round(x, 2)}%')

# 重命名表头
columns = ['代码', '名称', '日期', '收盘价', '涨跌幅', '主力流入', '主力持续流入天数', '主力累计流入', '累计涨跌幅']
columns_shaped = ['代码', '名称', '日期', '收盘价', '涨跌幅', '主力流入', '主力累计流入', '累计涨跌幅', '主力持续流入天数']

df_code_continue_latest.columns = columns
df_code_continue_latest = df_code_continue_latest[columns_shaped]

# 查一下市值
sql_shizhi = """
SELECT 
`code` as "代码",
market_value_str_flow as "流通市值",
market_value_str_all  as "总市值"
FROM stock.stock_basic_info_d
WHERE day_no=(SELECT MAX(day_no) FROM stock.stock_basic_info_d)
AND `code` in ({})
""".format(df_code_continue_latest['代码'].to_list()).replace('[', '').replace(']', '')

df_code_shizhi = pd.read_sql(sql_shizhi, conn)

df_code_continue_latest2 = pd.merge(df_code_continue_latest, df_code_shizhi, on="代码")


def calculate_rate(item):
    leiji = change_unit(item[0])
    liutong = change_unit(item[1])
    rate = round(leiji / liutong, 4)
    return rate


df_code_continue_latest2['累计流入占流通市值比例'] = df_code_continue_latest2[['主力累计流入', '流通市值']].apply(calculate_rate, axis=1)

df_code_continue_latest2.to_excel(f'data/df_code_continue_latest_{str(datetime.date.today())}_{datetime.datetime.now().strftime("%H%M%S")}_{int(time.time()) % 1000}.xlsx',
                                  index=False)

# TODO 连续的清单
# 重新生成df
df_code_continue_sorted = pd.concat([df_code_continue[df_code_continue['code'] == x] for x in df_code_continue_latest['代码']])

# 处理内容格式
df_code_continue_sorted['change_rate'] = df_code_continue_sorted['change_rate'].map(lambda x: f'{x}%')
df_code_continue_sorted['main_money_num'] = df_code_continue_sorted['main_money_num'].apply(change_money_unit)
df_code_continue_sorted['money_continue_sum'] = df_code_continue_sorted['money_continue_sum'].apply(change_money_unit)
df_code_continue_sorted['change_rate_sum'] = df_code_continue_sorted['change_rate_sum'].map(lambda x: f'{round(x, 2)}%')
# 重命名表头
df_code_continue_sorted.columns = columns
df_code_continue_sorted = df_code_continue_sorted[columns_shaped]

df_code_continue_sorted.to_excel(f'data/df_code_continue_sorted_{str(datetime.date.today())}_{datetime.datetime.now().strftime("%H%M%S")}_{int(time.time()) % 1000}.xlsx',
                                 index=False)

cursor.close()
conn.close()
